﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using Oracle.ManagedDataAccess.Client;
using System.Collections;
using System.Data.Common;
using NetUtilityLib;
using System.Data;
using DMSMT;

namespace db
{
    class Dao
    {

        public static string GetAsCode(string netcode)
        {
            string sql = "select agents_code from t_sys_organization where org_id=" + netcode;
            return Dao.selectStr("zj", sql);
        }

        public static string GetNetCode(string orderCode)
        {
            string sql = "select substr(tp.quoteid,0,4) netcode from ti_pt_part_order tp where tp.billcode like '%" + orderCode + "%'";
            return Dao.selectStr("zj", sql);
        }

        public static bool ExecuteSqlTran(List<String> SQLList, string ip)
        {
            DbConnection conn = null;
            DbCommand cmd = null;
            DbTransaction tx = null;
            try
            {
                conn = GetCommonConn(ip);
                cmd = conn.CreateCommand();
                tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                foreach (string sql in SQLList)
                {
                    cmd.CommandText = sql;
                    cmd.ExecuteNonQuery();
                }
                tx.Commit();
                return true;
            }
            catch (Exception e)
            {
                tx.Rollback();
                Log.LogError(e.Message);
                return false;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
                if (conn != null)
                    conn.Close();
            }
        }

        public static bool ExecuteSql(string sql, string connName)
        {
            DbConnection conn = null;
            DbCommand cmd = null;
            try
            {
                conn = GetConn(connName);
                conn.Open();
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                Log.LogError(e.Message);
                return false;
            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
                if (conn != null)
                    conn.Close();
            }
        }

        public static void ExecuteSql(string sql, DbConnection conn)
        {
            using (conn)
            {
                DbCommand cmd = null;
                conn.Open();
                cmd = conn.CreateCommand();
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
            }
        }

        /*
          string connString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=130.147.246.144)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ECMS)));Persist Security Info=True;User ID=system;Password=Service01;";           
        */
        public static DbConnection GetConn(string name)
        {
            string connString = Config.GetConnStr(name);
            DbConnection conn = new OracleConnection(connString);
            return conn;
        }

        public static DbConnection GetCommonConn(string ip,string username,string pwd)
        {
            string connString = string.Format(Config.GetConnStr("common"), ip, username, pwd);
            DbConnection conn = new OracleConnection(connString);
            return conn;
        }

        public static DbConnection GetCommonConn(string ip)
        {
            return GetCommonConn(ip,"dms","dmsbsj123");
        }

        private static void LogSql(string sql)
        {
            Log.LogText("执行sql:\t" + sql);
        }

        public static List<Hashtable> search(string connName, string sql)
        {
            DbConnection conn = GetConn(connName);
            return search(conn, sql);
        }
        public static List<Hashtable> search(DbConnection conn, string sql)
        {
            List<Hashtable> list = null;
            Hashtable ht = null;
            try
            {
                conn.Open();
                list = new List<Hashtable>();
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    LogSql(sql);
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            ht = new Hashtable();
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                ht.Add(reader.GetName(i).ToLower(), reader.GetValue(i).ToString());
                            }
                            list.Add(ht);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return list;
        }

        public static Hashtable select(string connName, string sql)
        {
            DbConnection conn = GetConn(connName);
            return select(conn, sql);
        }

        public static int update(string connName, string sql)
        {
            int res = 0;
            DbConnection conn = null;
            try
            {
                conn = GetConn(connName);
                conn.Open();
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    LogSql(sql);
                    res = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                Log.LogError(e.Message);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return res;

        }
        public static Hashtable select(DbConnection conn, string sql)
        {
            Hashtable ht = null;
            ht = new Hashtable();
            try
            {
                conn.Open();
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    LogSql(sql);
                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            ht = new Hashtable();
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                ht.Add(reader.GetName(i).ToLower(), reader.GetValue(i).ToString());
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return ht;
        }

        public static int selectInt(string connName, string sql)
        {
            string str = selectStr(connName, sql);
            if (str == null)
                return 0;
            return Int32.Parse(str);
        }

        public static string selectStr(string connName, string sql)
        {
            Object obj = selectObject(connName, sql);
            if (obj == null)
                return null;
            return obj.ToString();
        }

        public static Object selectObject(string connName, string sql)
        {
            DbConnection conn = GetConn(connName);
            return selectObject(conn, sql);
        }
        public static Object selectObject(DbConnection conn, string sql)
        {
            Object obj = null;
            try
            {
                conn.Open();
                using (DbCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    LogSql(sql);
                    obj = cmd.ExecuteScalar();
                }
            }
            catch (Exception ex)
            {
                Log.LogError(ex.Message);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return obj;
        }

        public static DataSet searchDS(DbConnection conn, string sql)
        {
            DataSet ds = new DataSet();
            try
            {
                DbDataAdapter ad = new OracleDataAdapter(sql, (OracleConnection)conn);
                ad.Fill(ds);
                ad.Dispose();
            }
            catch (Exception e)
            {
                Log.LogError(e.Message);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return ds;
        }


        public static DataSet searchDS(string connName, string sql)
        {
            LogSql(sql);
            DataSet ds = null;
            DbConnection conn = null;
            try
            {
                conn = GetConn(connName);
                DbDataAdapter ad = new OracleDataAdapter(sql, (OracleConnection)conn);
                ds = new DataSet();
                ad.Fill(ds);
                ad.Dispose();
            }
            catch (Exception e)
            {
                Log.LogError(e.Message);
            }
            finally
            {
                if (conn != null)
                    conn.Close();
            }
            return ds;
        }
    }
}
